-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE NTDB
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Sushil Mittal
-- Create date: 
-- Description:	
-- =============================================
IF OBJECT_ID('sp_NTDB_ExtractRelevantCols') IS NOT NULL
    DROP PROCEDURE sp_NTDB_ExtractRelevantCols

GO
CREATE PROCEDURE sp_NTDB_ExtractRelevantCols 
	-- Add the parameters for the stored procedure here
	--@TableName NVARCHAR(100)
AS
BEGIN
	-- Insert statements for procedure here	SET NOCOUNT ON;
SELECT

/*AISCODE      */ AISCODE.INC_KEY, AISCODE.AISCODE, AISCODE.AISSCORE, AISCODE.BODYREGION,
/*COMORBID     */ 
/*COMPLIC      */ 
/*DEMO         */ DEMO.AGE, DEMO.GENDER,
/*DIAGNOS      */ DIAGNOS.DCODE,
/*DIAGNOSISDESC*/
/*DISCHARGE    */ DISCHARGE.LOS, DISCHARGE.DISCHDISP, DISCHARGE.DISSTATUS,
/*ECODE        */ ECODE.ECODE,
/*ED           */ ED.EDSYSBP, ED.EDRESPRATE, ED.EDGCSEYE, ED.EDGCSVERB, ED.EDGCSMOTOR, ED.EDGCSTOTAL, ED.EDDISP,
/*EDITFLAGS    */
/*FACILITY	   */
/*FACILITY_INC */
/*IMPUTED      */
/*INTUB        */
/*MECHDESC     */
/*PREHPROC     */
/*PROCEDURE    */
/*PROCEDUREDESC*/
/*SAFETY       */
/*SCENE        */ SCENE.INJURYTYPE, SCENE.INJURYSITE

FROM 

RDS_AISCODE AS AISCODE
INNER JOIN
RDS_DEMO as DEMO
ON DEMO.INC_KEY = AISCODE.INC_KEY
INNER JOIN 
RDS_DIAGNOS as DIAGNOS
ON DEMO.INC_KEY = DIAGNOS.INC_KEY
INNER JOIN
RDS_DISCHARGE as DISCHARGE
ON DEMO.INC_KEY = DISCHARGE.INC_KEY
INNER JOIN
RDS_ECODE AS ECODE
ON DEMO.INC_KEY = ECODE.INC_KEY
INNER JOIN
RDS_ED as ED
ON DEMO.INC_KEY = ED.INC_KEY
INNER JOIN
RDS_SCENE AS SCENE
ON DEMO.INC_KEY = SCENE.INC_KEY

--FILTER BASED ON AGE
WHERE DEMO.AGE <= 14 AND DEMO.AGE >= 0
--REMOVE DUPLICATES
AND DISCHARGE.DISCHDISP != 'Hosp Transfer' AND ED.EDDISP != 'Transfer' 
--REMOVE BURNS
AND SCENE.INJURYTYPE != 'Burn' AND DISCHARGE.DISCHDISP != 'Burn'	AND ED.EDDISP != 'Burn'
					 AND AISCODE.AISCODE IS NOT NULL
					 AND AISCODE.BODYREGION IS NOT NULL				AND AISCODE.BODYREGION != 'NULL'
					 AND DEMO.INC_KEY IS NOT NULL 
					 AND DEMO.AGE IS NOT NULL 
					 AND DEMO.GENDER IS NOT NULL
					 AND DIAGNOS.DCODE IS NOT NULL
					 AND DISCHARGE.LOS IS NOT NULL					AND DISCHARGE.LOS >= 0
					 AND ECODE.ECODE IS NOT NULL
					 AND ED.EDSYSBP IS NOT NULL						AND ED.EDSYSBP >= 0
					 AND ED.EDRESPRATE IS NOT NULL					AND ED.EDRESPRATE >= 0
					 AND SCENE.INJURYTYPE IS NOT NULL
					 AND SCENE.INJURYSITE IS NOT NULL
--ORDER BY DISCHARGE.DISCHDISP
END
GO
